package com.zut.dao;

import com.zut.domain.Coach;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface CoachDao {

    @Insert("insert into coach " +
            "(coach_id,coach_password,coach_name,coach_sex,coach_age," +
            "coach_number,coach_mobile,practise_place,coach_date," +
            "coach_home,coach_introduction,coach_status,coach_subject) " +
            "values" +
            "(#{id},#{password},#{name},#{sex},#{age},#{idCard},#{mobile},#{place},#{date},#{home},#{introduction},#{status},#{subject})")
    int insert(Coach coach);


    @Select("select coach.coach_id as id,coach_name as name,coach_sex as sex," +
            "coach_age as age,coach_number as idCard,coach_mobile as mobile," +
            "practise_place as place,coach_img as img,coach_date as date,coach_home as home," +
            "coach_introduction as introduction,coach_status as status,coach_subject as subject," +
            "round(avg(evaluation_grade),2) as grade from coach " +
            "left join evaluation on coach.coach_id=evaluation.coach_id group by coach.coach_id")
    List<Map<String, String>> getAll();

    @Results(id = "groupWithCoach",
            value = {
                    @Result(property = "id", column = "coach_id", id = true),
                    @Result(property = "name", column = "coach_name"),
                    @Result(property = "sex", column = "coach_sex"),
                    @Result(property = "age", column = "coach_age"),
                    @Result(property = "idCard", column = "coach_number"),
                    @Result(property = "mobile", column = "coach_mobile"),
                    @Result(property = "place", column = "practise_place"),
                    @Result(property = "img", column = "coach_img"),
                    @Result(property = "date", column = "coach_date"),
                    @Result(property = "home", column = "coach_home"),
                    @Result(property = "status", column = "coach_status"),
                    @Result(property = "introduction", column = "coach_introduction"),
                    @Result(property = "subject", column = "coach_subject")
            })
    @Select("select * from coach where coach_id = #{id}")
    Coach selectById(String id);

    @Select("select coach.coach_id as id,coach_name as name,coach_sex as sex," +
            "coach_age as age,coach_number as idCard,coach_mobile as mobile," +
            "practise_place as place,coach_img as img,coach_date as date,coach_home as home," +
            "coach_introduction as introduction,coach_status as status,coach_subject as subject," +
            "round(avg(evaluation_grade),2) as grade from coach " +
            "left join evaluation on coach.coach_id=evaluation.coach_id where coach.coach_id = #{id} group by coach.coach_id")
    Map<String, String> getById(String id);

    @ResultMap("groupWithCoach")
    @Select("select * from coach where coach_id=#{id} and coach_password=#{password}")
    Coach getByNP(Coach coach);

    @Delete("delete from coach where coach_id = #{id};")
    int deleteById(String id);

    @Update("<script>                                                                      "
            + "update coach                                                                "
            + "<set>                                                                       "
            + "  <if test='name != null'> coach_name=#{name},                         </if>"
            + "  <if test='sex != null'> coach_sex=#{sex},                            </if>"
            + "  <if test='age != null'> coach_age=#{age},                            </if>"
            + "  <if test='idCard != null'> coach_number=#{idCard},                   </if>"
            + "  <if test='mobile != null'> coach_mobile=#{mobile},                   </if>"
            + "  <if test='place != null'> practise_place=#{place},                   </if>"
            + "  <if test='img != null'> coach_img=#{img},                            </if>"
            + "  <if test='date != null'> coach_date=#{date},                         </if>"
            + "  <if test='home != null'> coach_home=#{home},                         </if>"
            + "  <if test='introduction != null'> coach_introduction=#{introduction}, </if>"
            + "  <if test='status != null'> coach_status=#{status},                   </if>"
            + "  <if test='subject != null'> coach_subject=#{subject},                </if>"
            + "</set>                                                                      "
            + "where coach_id = #{id}                                                      "
            + "</script>                                                                   ")
    int update(Coach coach);

    @Select("select practise_place from coach where coach_id = #{id}")
    String getPlace(String id);

    @Select("select coach_password from coach where coach_id = #{id}")
    String getPassword(String id);

    @ResultMap("groupWithCoach")
    @Select("select coach.coach_id,coach_name,coach_sex,coach_age,coach_number,coach_mobile," +
            "practise_place,coach_img,coach_date,coach_home,coach_introduction,coach_status," +
            "coach_subject from coach,evaluation where coach.coach_id = evaluation.coach_id " +
            "group by coach.coach_id " +
            "having avg(evaluation_grade)=5 ")
    List<Coach> getBestCoach();

    @Select("select coach_name as name," +
            "coach_sex as sex," +
            "coach_age as age," +
            "coach_mobile as idCard," +
            "practise_place as place," +
            "coach_img as img," +
            "coach_date as date," +
            "coach_home as home," +
            "coach_introduction as introduction," +
            "coach_subject as subject," +
            "round(avg(evaluation_grade),2) as grade " +
            "from coach " +
            "left join evaluation on coach.coach_id = evaluation.coach_id " +
            "where coach.coach_id = #{id}")
    Map<String,String> getAvgGradeById(String id);


    @Update("<script>                                                                      "
            + "update coach                                                                "
            + "<set>                                                                       "
            + "  <if test='params.newPassword != null'> coach_password=#{params.newPassword},                         </if>"
            + "</set>                                                                      "
            + "where coach_id = #{params.id}                                                      "
            + "</script>                                                                   ")
    int updatePwd(@Param("params") HashMap<String, String> map);
}
